package net.apexes.commons.ormlite;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.j256.ormlite.logger.Logger;
import com.j256.ormlite.logger.LoggerFactory;
import com.j256.ormlite.support.ConnectionSource;
import net.apexes.commons.lang.Checks;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author <a href=mailto:hedyn@foxmail.com>HeDYn</a>
 */
public class SqliteUpgrader implements UpgradeChecker {

    private static final Logger LOG = LoggerFactory.getLogger(SqliteUpgrader.class);

    private final SQLiteDatabase db;
    private final List<Table<?>> tables;

    public SqliteUpgrader(SQLiteDatabase db, List<Table<?>> tables) {
        this.db = db;
        this.tables = tables;
    }

    void upgrade(ConnectionSource connectionSource) throws Exception {
        db.beginTransaction();
        try {
            UpgradeHelper upgradeHelper = new UpgradeHelper(connectionSource, this);
            for (Table<?> table : tables) {
                upgradeHelper.upgrade(table);
                upgradeIndex(table);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void upgradeIndex(Table<?> table) throws Exception {
        Class<?> tableClass = table.getClass();

        Map<String, String> needCreateMap = new HashMap<>();
        Map<String, Boolean> nameUniqueMap = new HashMap<>();

        // 索引
        Field ixsField = null;
        try {
            ixsField = tableClass.getField("ix");
        } catch (Exception e) {
            // ignore
        }
        if (ixsField != null) {
            Object ixs = ixsField.get(table);
            for (Field field : ixs.getClass().getFields()) {
                if (Index.class.isAssignableFrom(field.getType())) {
                    Index ix = (Index) field.get(ixs);
                    String ixName = ix.name().toUpperCase();
                    StringBuilder sb = new StringBuilder();
                    List<Index.IndexColumn> ics = ix.columns();
                    for (int i = 0; i < ics.size(); i++) {
                        if (i > 0) {
                            sb.append(", ");
                        }
                        Index.IndexColumn ic = ics.get(i);
                        sb.append(ic.getColumn().name().toUpperCase());
                        if (ic.isDesc()) {
                            sb.append(" DESC");
                        }
                    }
                    needCreateMap.put(sb.toString(), ixName);
                    nameUniqueMap.put(ixName, ix.isUnique());
                }
            }
        }

        // 外键
        Field fksField = null;
        try {
            fksField = tableClass.getField("fk");
        } catch (Exception e) {
            // ignore
        }
        if (fksField != null) {
            Object fks = fksField.get(table);
            for (Field field : fks.getClass().getFields()) {
                if (ForeignKey.class.isAssignableFrom(field.getType())) {
                    ForeignKey fk = (ForeignKey) field.get(fks);
                    String columnName = fk.column().name().toUpperCase();
                    if (!needCreateMap.containsKey(columnName)) {
                        String indexName = "IX_" + table.name() + "_PK_" + columnName;
                        indexName = indexName.toUpperCase();
                        needCreateMap.put(columnName, indexName);
                        nameUniqueMap.put(indexName, false);
                    }
                }
            }
        }

        Set<String> needDeleteNames = new HashSet<>();

        String selectSql = new StringBuilder()
                .append("select name, sql from sqlite_master")
                .append(" where type='index' and sql is not null")
                .append(" and tbl_name='").append(table.name()).append("'")
                .toString();
        try (Cursor cursor = db.rawQuery(selectSql, null)) {
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    String name = cursor.getString(0).trim().toUpperCase();
                    String sql = cursor.getString(1).trim().toUpperCase();

                    boolean unique = sql.contains(" UNIQUE ");
                    String columnInfo = parseColumn(sql);
                    String needCreateName = needCreateMap.get(columnInfo);
                    if (needCreateName == null) {
                        // Q类中没有的索引要删除
                        needDeleteNames.add(name);
                    } else if (nameUniqueMap.get(needCreateName)) {
                        // Q类中是唯一约束索引时，数据库也是唯一约束就不需要再创建，否则删除数据库中的重新创建
                        if (unique) {
                            needCreateMap.remove(columnInfo);
                            nameUniqueMap.remove(needCreateName);
                        } else {
                            needDeleteNames.add(name);
                        }
                    } else if (unique) {
                        // 数据库中是唯一约束，但Q类中不是唯一约束，删除数据库中的重新创建
                        needDeleteNames.add(name);
                    } else {
                        needCreateMap.remove(columnInfo);
                        nameUniqueMap.remove(needCreateName);
                    }

                    // 如果数据库中已有索引与要新增的索引同名就删除之
                    if (nameUniqueMap.containsKey(name)) {
                        needDeleteNames.add(name);
                    }
                }
            }
        }

        // 先删除索引然后再创建
        if (Checks.isNotEmpty(needDeleteNames)) {
            for (String indexName : needDeleteNames) {
                String sql = new StringBuilder()
                        .append("DROP INDEX IF EXISTS ")
                        .append(indexName)
                        .toString();
                LOG.info(sql);
                db.execSQL(sql);
            }
        }
        if (Checks.isNotEmpty(needCreateMap)) {
            for (Map.Entry<String, String> entry : needCreateMap.entrySet()) {
                String columnInfo = entry.getKey();
                String indexName = entry.getValue();

                StringBuilder sb = new StringBuilder();
                sb.append("DROP INDEX IF EXISTS ").append(indexName);
                String dropSql = sb.toString();
                LOG.info(dropSql);
                db.execSQL(dropSql);

                sb.setLength(0);
                if (nameUniqueMap.get(indexName)) {
                    sb.append("CREATE UNIQUE INDEX ");
                } else {
                    sb.append("CREATE INDEX ");
                }
                sb.append(indexName)
                        .append(" ON ")
                        .append(table.name())
                        .append(" (")
                        .append(columnInfo)
                        .append(")");
                String createSql = sb.toString();
                LOG.info(createSql);
                db.execSQL(createSql);
            }
        }
    }

    private String parseColumn(String sql) {
        StringBuilder sb = new StringBuilder();

        int bi = sql.indexOf("(") + 1;
        int ei = sql.indexOf(")");
        String allColumnInfo = sql.substring(bi, ei);
        String[] columnInfoArr = allColumnInfo.split(",");
        for (int i = 0; i < columnInfoArr.length; i++) {
            if (i > 0) {
                sb.append(", ");
            }
            String columnInfo = columnInfoArr[i].trim();
            int iDesc = columnInfo.indexOf(" DESC");
            if (iDesc > 0) {
                String columnName = columnInfo.substring(0, iDesc).trim();
                sb.append(columnName).append(" DESC");
            } else {
                int iSpace = columnInfo.indexOf(" ");
                if (iSpace > 0) {
                    String columnName = columnInfo.substring(0, iSpace).trim();
                    sb.append(columnName);
                } else {
                    sb.append(columnInfo);
                }
            }
        }

        return sb.toString();
    }

    @Override
    public boolean exists(ConnectionSource connectionSource, Table<?> table) throws Exception {
        String selectSql = new StringBuilder()
                .append("select count(*) as c from sqlite_master")
                .append(" where type='table' and tbl_name='").append(table.name()).append("'")
                .toString();
        try (Cursor cursor = db.rawQuery(selectSql, null)) {
            return cursor != null && cursor.moveToNext() && cursor.getInt(0) > 0;
        }
    }

    @Override
    public boolean exists(ConnectionSource connectionSource, Column column, Field field) throws Exception {
        String selectSql = new StringBuilder()
                .append("select * from ").append(column.table().name())
                .append(" limit 0")
                .toString();
        try (Cursor cursor = db.rawQuery(selectSql, null)) {
            return cursor != null && cursor.getColumnIndex(column.name()) != -1;
        }
    }
}
